﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using KPIReport.Library;
using KPIReport.Library.DomainObject.Administration.Catalog;
using KPIReport.Library.Enum;
using KPIReport.Library.Interfaces;

namespace KPIReport.Library.DataAccess.Administration.Catalog
{
    public partial class customerRepo
    {
        public bool SaveUpload(List<customer> domains)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    domains.ForEach(delegate(customer domain)
                    {
                        SqlCommand cmd = conn.CreateCommand();
                        cmd.CommandText = @"INSERT INTO dbo.customer_temp(
                        [Customer Code]
                        ,[Customer Name]
                        ,[Kode Outlet Classification]
                        ,[Outlet Classification]
                        ,[Customer Open Date]
                        ,[Customer Status]
                        ,[Postal Code]
                        ,[Kode Customer Area]
                        ,[Customer Area]
                        ,[Contact Person]
                        ,[Contact No#]
                        ,[F12]
                        ,[Address]
                        ,[Credit Status]
                        ,[Credit Limit  (Rp)]
                        ,[Credit Term]
                        ,[F17]
                        ,[F18]
                        ,[F19]
                        ) 
                        VALUES(
                        @Customer_Code
                        , @Customer_Name
                        , @Kode_Outlet_Classification
                        , @Outlet_Classification
                        , @Customer_Open_Date
                        , @Customer_Status
                        , @Postal_Code
                        , @Kode_Customer_Area
                        , @Customer_Area
                        , @Contact_Person
                        , @Contact_No
                        , @F12
                        , @Address
                        , @Credit_Status
                        , @Credit_Limit__Rp
                        , @Credit_Term
                        , @F17
                        , @F18
                        , @F19
                        )";
                        cmd.CommandType = CommandType.Text;

                        //cmd.CommandText = "INSERT INTO dbo.customer(Customer Code, Customer Name, Kode Outlet Classification, Outlet Classification, Customer Open Date, Customer Status, Postal Code, Kode Customer Area, Customer Area, Contact Person, Contact No#, F12, Address, Credit Status, Credit Limit  (Rp), Credit Term, F17, F18, F19) VALUES(@Customer Code, @Customer Name, @Kode Outlet Classification, @Outlet Classification, @Customer Open Date, @Customer Status, @Postal Code, @Kode Customer Area, @Customer Area, @Contact Person, @Contact No#, @F12, @Address, @Credit Status, @Credit Limit  (Rp), @Credit Term, @F17, @F18, @F19)";
                        //cmd.CommandText = "SP_customer_Insert";
                        //cmd.CommandType = CommandType.StoredProcedure;
                        
                        cmd.Parameters.Add("@Customer_Code", SqlDbType.NVarChar).Value = (object)domain.Customer_Code ?? DBNull.Value;
                        cmd.Parameters.Add("@Customer_Name", SqlDbType.NVarChar).Value = (object)domain.Customer_Name ?? DBNull.Value;
                        cmd.Parameters.Add("@Kode_Outlet_Classification", SqlDbType.NVarChar).Value = (object)domain.Kode_Outlet_Classification ?? DBNull.Value;
                        cmd.Parameters.Add("@Outlet_Classification", SqlDbType.NVarChar).Value = (object)domain.Outlet_Classification ?? DBNull.Value;
                        cmd.Parameters.Add("@Customer_Open_Date", SqlDbType.NVarChar).Value = (object)domain.Customer_Open_Date ?? DBNull.Value;
                        cmd.Parameters.Add("@Customer_Status", SqlDbType.NVarChar).Value = (object)domain.Customer_Status ?? DBNull.Value;
                        cmd.Parameters.Add("@Postal_Code", SqlDbType.NVarChar).Value = (object)domain.Postal_Code ?? DBNull.Value;
                        cmd.Parameters.Add("@Kode_Customer_Area", SqlDbType.NVarChar).Value = (object)domain.Kode_Customer_Area ?? DBNull.Value;
                        cmd.Parameters.Add("@Customer_Area", SqlDbType.NVarChar).Value = (object)domain.Customer_Area ?? DBNull.Value;
                        cmd.Parameters.Add("@Contact_Person", SqlDbType.NVarChar).Value = (object)domain.Contact_Person ?? DBNull.Value;
                        cmd.Parameters.Add("@Contact_No", SqlDbType.NVarChar).Value = (object)domain.Contact_No ?? DBNull.Value;
                        cmd.Parameters.Add("@F12", SqlDbType.NVarChar).Value = (object)domain.F12 ?? DBNull.Value;
                        cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = (object)domain.Address ?? DBNull.Value;
                        cmd.Parameters.Add("@Credit_Status", SqlDbType.NVarChar).Value = (object)domain.Credit_Status ?? DBNull.Value;
                        cmd.Parameters.Add("@Credit_Limit__Rp", SqlDbType.NVarChar).Value = (object)domain.Credit_Limit__Rp ?? DBNull.Value;
                        cmd.Parameters.Add("@Credit_Term", SqlDbType.NVarChar).Value = (object)domain.Credit_Term ?? DBNull.Value;
                        cmd.Parameters.Add("@F17", SqlDbType.NVarChar).Value = (object)domain.F17 ?? DBNull.Value;
                        cmd.Parameters.Add("@F18", SqlDbType.NVarChar).Value = (object)domain.F18 ?? DBNull.Value;
                        cmd.Parameters.Add("@F19", SqlDbType.NVarChar).Value = (object)domain.F19 ?? DBNull.Value;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    });

                   SqlCommand cmdDua = conn.CreateCommand();
                   cmdDua.CommandText = "delete from customer_temp where [Customer Code] is null or [Customer Name] is null";
                   cmdDua.CommandType = CommandType.Text;

                   cmdDua.Transaction = txn;
                   cmdDua.ExecuteNonQuery();
                   cmdDua.Dispose();

                   cmdDua = conn.CreateCommand();
                   cmdDua.CommandText = "delete customer where [customer code] in(select [customer code] from customer_temp";
                   cmdDua.CommandType = CommandType.Text;

                   cmdDua.Transaction = txn;
                   cmdDua.ExecuteNonQuery();
                   cmdDua.Dispose();

                   cmdDua = conn.CreateCommand();
                   cmdDua.CommandText = "INSERT INTO customer SELECT * FROM customer_temp ";
                   cmdDua.CommandType = CommandType.Text;

                   cmdDua.Transaction = txn;
                   cmdDua.ExecuteNonQuery();
                   cmdDua.Dispose();

                   


                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }


            }
            return result;
        }

        public List<customer> GetList(int pageSize, int currentPage, string sortName, string sortOrder,string parameters)
        {
            List<customer> tt = new List<customer>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);


					//cmd.CommandText = "SP_customer_GridPage";
					

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("       [customer code],[Customer Name], [Outlet Classification] ");
                    sbQuery.AppendFormat("    FROM ");
					sbQuery.AppendFormat("      dbo.customer ");
                    sbQuery.AppendFormat("      {0} ",parameters);
                    //sbQuery.AppendFormat("      ORDER BY  [Customer Name] ");
					sbQuery.AppendFormat(" ) ");
					sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    
					
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                customer t = new customer();
                                t.Customer_Code = dr[customer.Fields.Customer_Code] == DBNull.Value ? string.Empty : dr[customer.Fields.Customer_Code].ToString();
                                t.Customer_Name = dr[customer.Fields.Customer_Name] == DBNull.Value ? string.Empty : dr[customer.Fields.Customer_Name].ToString();
                                t.Outlet_Classification = dr[customer.Fields.Outlet_Classification] == DBNull.Value ? string.Empty : dr[customer.Fields.Outlet_Classification].ToString();
                                
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }


        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {

                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM dbo.customer {1} ", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

    }
}
